/*
 * $RCSfile: TableHandler.java,v $
 * $Revision: 1.1 $
 * $Date: 2009-2-16 $
 *
 * Copyright (C) 2005 Skin, Inc. All rights reserved.
 *
 * This software is the proprietary information of Skin, Inc.
 * Use is subject to license terms.
 */

package com.skin.webcat.database.handler;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.skin.database.Jdbc;
import com.skin.webcat.database.Column;
import com.skin.webcat.database.Table;
import com.skin.webcat.database.IndexInfo;
import com.skin.webcat.database.dialect.Dialect;
import com.skin.webcat.util.ClassUtil;
import com.skin.webcat.util.StringUtil;

/**
 * <p>Title: TableHandler</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2006</p>
 * @author xuesong.net
 * @version 1.0
 */
public class TableHandler {
    private Dialect dialect;
    private Connection connection;
    private static Logger logger = LoggerFactory.getLogger(TableHandler.class);

    /**
     * @param connection
     */
    public TableHandler(Connection connection) {
        this.connection = connection;
        this.setDialect(this.getDialect(connection));
    }

    /**
     * @param connection
     */
    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    /**
     * @param dialect
     */
    public void setDialect(Dialect dialect) {
        this.dialect = dialect;
    }

    /**
     * @return Dialect
     */
    public Dialect getDialect() {
        return this.dialect;
    }

    /**
     * @param connection
     * @return List<String>
     * @throws SQLException
     */
    public List<String> getDatabase(Connection connection) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet resultSet = metaData.getCatalogs();
        List<String> names = new ArrayList<String>();

        while(resultSet.next()) {
            names.add(resultSet.getString("table_cat"));
        }
        java.util.Collections.sort(names);
        return names;
    }

    /**
     * @param tableName
     * @return Table
     * @throws SQLException
     */
    public Table getTable(String tableName) throws SQLException {
        return this.getTable(tableName, new String[]{"TABLE", "VIEW"});
    }

    /**
     * @param tableName
     * @param types
     * @return Table
     * @throws SQLException
     */
    public Table getTable(String tableName, String[] types) throws SQLException {
        return this.getTable(null, null, tableName, types);
    }

    /**
     * @param catalog
     * @param schema
     * @param tableName
     * @param types
     * @return Table
     * @throws SQLException
     */
    public Table getTable(String catalog, String schema, String tableName, String[] types) throws SQLException {
        List<Table> list = this.getTableList(catalog, schema, tableName, types, true);

        if(list != null && !list.isEmpty()) {
            return list.get(0);
        }
        else {
            return null;
        }
    }

    /**
     * @param pattern
     * @param types
     * @param flag
     * @return List<Table>
     * @throws SQLException
     */
    public List<Table> getTableList(String pattern, String[] types, boolean flag) throws SQLException {
        return this.getTableList(null, null, pattern, types, flag);
    }

    /**
     * @param catalog
     * @param schema
     * @param pattern
     * @param types
     * @param flag
     * @return List<Table>
     * @throws SQLException
     */
    public List<Table> getTableList(String catalog, String schema, String pattern, String[] types, boolean flag) throws SQLException {
        ResultSet resultSet = null;
        List<Table> tableList = new ArrayList<Table>();
        logger.debug("catalog: {}, schema: {}, pattern: {}", catalog, schema, pattern);

        try {
            DatabaseMetaData metaData = this.connection.getMetaData();
            resultSet = metaData.getTables(catalog, schema, pattern, types);

            while(resultSet.next()) {
                String tableName = resultSet.getString("TABLE_NAME");
                String tableType = resultSet.getString("TABLE_TYPE");
                String remarks = resultSet.getString("REMARKS");
                String className = this.toCamel(tableName);
                String variableName = Character.toLowerCase(className.charAt(0)) + className.substring(1);
                logger.debug("tableName: {}, remarks: {}", tableName, remarks);

                Table table = new Table();
                table.setTableCode(tableName);
                table.setTableName(tableName);
                table.setTableType(tableType);
                table.setRemarks(remarks);
                table.setQueryName(tableName);
                table.setClassName(className);
                table.setVariableName(variableName);
                tableList.add(table);
            }

            if(flag) {
                for(Table table : tableList) {
                    String tableName = table.getTableName();
                    List<String> primaryKeys = this.getPrimaryKeys(metaData, tableName);
                    List<Column> columns = this.getColumns(metaData, schema, tableName);
                    table.setColumns(columns);

                    for(Column column : columns) {
                        String columnName = column.getColumnName();

                        if(primaryKeys.contains(columnName)) {
                            column.setPrimaryKey(true);
                        }
                        else {
                            column.setPrimaryKey(false);
                        }
                    }
                }
            }
        }
        finally {
            this.close(resultSet);
        }
        return tableList;
    }

    /**
     * @param metaData
     * @return Table
     * @throws SQLException
     */
    public Table getTable(ResultSetMetaData metaData)  throws SQLException {
        Table table = new Table();
        int columnCount = metaData.getColumnCount();

        for(int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            int dataType = metaData.getColumnType(i);
            String typeName = metaData.getColumnTypeName(i);
            int columnSize = metaData.getColumnDisplaySize(i);
            int decimalDigits = metaData.getPrecision(i);
            String variable = java.beans.Introspector.decapitalize(this.toCamel(columnName));

            if("ID".equals(variable)) {
            }
            else {
                variable = Character.toLowerCase(variable.charAt(0)) + variable.substring(1);
            }

            Column column = new Column();
            column.setTable(null);
            column.setColumnCode(columnName);
            column.setColumnName(columnName);
            column.setDataType(dataType);
            column.setTypeName(typeName);
            column.setColumnSize(columnSize);
            column.setDecimalDigits(decimalDigits);
            column.setNullable(true);
            column.setRemarks("");
            column.setColumnDef("");
            column.setPrimaryKey(false);
            column.setVariableName(variable);
            column.setMethodSetter("set" + this.toCamel(columnName));
            column.setMethodGetter("get" + this.toCamel(columnName));
            this.dialect.process(column);
            table.add(column);
        }
        return table;
    }

    /**
     * @param metaData
     * @param tableName
     * @return List<String>
     * @throws SQLException
     */
    public List<String> getPrimaryKeys(DatabaseMetaData metaData, String tableName) throws SQLException {
        ResultSet resultSet = null;
        List<String> primaryKeys = new ArrayList<String>();

        try {
            resultSet = metaData.getPrimaryKeys(null, null, tableName);

            while(resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                primaryKeys.add(columnName);
            }
        }
        finally {
            this.close(resultSet);
        }
        return primaryKeys;
    }
    
    /**
     * @param metaData
     * @param schema
     * @param tableName
     * @return Column
     * @throws SQLException 
     */
    public List<Column> getColumns(DatabaseMetaData metaData, String schema, String tableName) throws SQLException {
        ResultSet resultSet = null;
        List<Column> columns = new ArrayList<Column>();

        try {
            resultSet = metaData.getColumns(null, schema, tableName, "%");

            while(resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                String variable = java.beans.Introspector.decapitalize(this.toCamel(columnName));

                if("ID".equals(variable)) {
                }
                else {
                    variable = Character.toLowerCase(variable.charAt(0)) + variable.substring(1);
                }

                int dataType = resultSet.getInt("DATA_TYPE");
                String typeName = resultSet.getString("TYPE_NAME");
                boolean autoIncrement = resultSet.getBoolean("IS_AUTOINCREMENT");
                int columnSize = resultSet.getInt("COLUMN_SIZE");
                int decimalDigits = resultSet.getInt("DECIMAL_DIGITS");
                String columnDef = resultSet.getString("COLUMN_DEF");
                String columnRemark = resultSet.getString("REMARKS");
                boolean nullable = resultSet.getBoolean("NULLABLE");

                logger.debug("column: " + columnName);
                Column column = new Column();
                column.setColumnCode(columnName);
                column.setColumnName(columnName);
                column.setDataType(dataType);
                column.setTypeName(typeName);
                column.setAutoIncrement(autoIncrement);
                column.setColumnSize(columnSize);
                column.setDecimalDigits(decimalDigits);
                column.setNullable(nullable);
                column.setRemarks(columnRemark);
                column.setColumnDef(columnDef);
                column.setVariableName(variable);
                column.setMethodSetter("set" + this.toCamel(columnName));
                column.setMethodGetter("get" + this.toCamel(columnName));
                this.dialect.process(column);
                columns.add(column);
            }
        }
        finally {
            this.close(resultSet);
        }
        return columns;
    }

    /**
     * @param tableName
     * @return List<IndexInfo>
     * @throws SQLException
     */
    public List<IndexInfo> getIndexInfoList(String tableName) throws SQLException {
        ResultSet resultSet = null;
        List<IndexInfo> indexInfoList = new ArrayList<IndexInfo>();

        try {
            DatabaseMetaData metaData = this.connection.getMetaData();
            resultSet = metaData.getIndexInfo(null, null, tableName, false, true);

            while(resultSet.next()) {
                /**
                 * indexType: jdbc中定义的indexType为int类型
                 */
                IndexInfo indexInfo = new IndexInfo();
                indexInfo.setCatalog(resultSet.getString("TABLE_CAT"));
                indexInfo.setTableSchem(resultSet.getString("TABLE_SCHEM"));
                indexInfo.setTableName(resultSet.getString("TABLE_NAME"));
                indexInfo.setNonUnique((resultSet.getBoolean("NON_UNIQUE") ? 1 : 0));
                indexInfo.setIndexQualifier(resultSet.getString("INDEX_QUALIFIER"));
                indexInfo.setIndexName(resultSet.getString("INDEX_NAME"));
                indexInfo.setIndexType(resultSet.getString("TYPE"));
                indexInfo.setOrdinalPosition(resultSet.getInt("ORDINAL_POSITION"));
                indexInfo.setColumnName(resultSet.getString("COLUMN_NAME"));
                indexInfo.setAscOrDesc(resultSet.getString("ASC_OR_DESC"));
                indexInfo.setCardinality(resultSet.getInt("CARDINALITY"));
                indexInfo.setPages(resultSet.getInt("PAGES"));
                indexInfo.setFilterCondition(resultSet.getString("FILTER_CONDITION"));
                indexInfoList.add(indexInfo);
            }
            return indexInfoList;
        }
        finally {
            Jdbc.close(resultSet);
        }
    }

    /**
     * @param t1
     * @param t2
     */
    public void addColumns(Table t1, Table t2) {
        if(t1.getPrimaryKey() == null) {
            t1.getPrimaryKeys().addAll(t2.getPrimaryKeys());
        }

        if(t1.getColumns() != null) {
            t1.getColumns().addAll(t2.getColumns());
        }
    }

    /**
     * @param connection
     * @return Dialect
     */
    public Dialect getDialect(Connection connection) {
        String productName = null;

        try {
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            productName = databaseMetaData.getDatabaseProductName();
        }
        catch(SQLException e) {
            e.printStackTrace();
        }

        if(productName == null) {
            return null;
        }

        String className = null;
        productName = productName.trim();

        if(logger.isDebugEnabled()) {
            logger.debug("DataBase ProductName: " + productName);
        }

        if(productName.length() > 1) {
            productName = StringUtil.replace(productName, " ",  "").toLowerCase();
            String[] names = {"DB2", "HSQL", "MySQL", "Oracle", "Oracle11g", "Oracle10g", "Oracle9", "SQLServer", "Access", "TDDL"};

            for(int i = 0; i < names.length; i++) {
                if(productName.indexOf(names[i].toLowerCase()) > -1) {
                    className = Dialect.class.getName();
                    className = className.substring(0, className.length() - Dialect.class.getSimpleName().length());
                    className = className + names[i] + "Dialect";
                    break;
                }
            }
        }

        if(logger.isDebugEnabled()) {
            logger.debug("DataBase Dialect: " + className + "\r\n");
        }

        Dialect dialect = null;

        try {
            dialect = (Dialect)(ClassUtil.getInstance(className, Dialect.class));
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        return dialect;
    }

    /**
     * @param list
     */
    public void print(List<Table> list) {
        for(Iterator<Table> iterator = list.iterator(); iterator.hasNext();) {
            Table table = iterator.next();
            System.out.println(table.toString());
        }
    }
    
    /**
     * @param name
     * @return String
     */
    public String toCamel(String name) {
        if(null == name || name.trim().length() < 1) {
            return "";
        }

        String[] subs = name.split("_");
        StringBuilder buffer = new StringBuilder();

        if(name.startsWith("_")) {
            buffer.append("_");
        }

        if(subs.length == 1) {
            String s = subs[0];

            if("ID".equals(s)) {
                buffer.append("Id");
            }
            else if(s.toUpperCase().equals(s)) {
                buffer.append(Character.toUpperCase(s.charAt(0)));
                buffer.append(s.substring(1).toLowerCase());
            }
            else {
                buffer.append(Character.toUpperCase(s.charAt(0))).append(s.substring(1));
            }
        }
        else {
            for(String s : subs) {
                if(s.length() > 0) {
                    // buffer.append(Character.toUpperCase(s.charAt(0))).append(s.substring(1).toLowerCase());

                    if("ID".equals(s)) {
                        buffer.append(s);
                    }
                    else if(s.toUpperCase().equals(s)) {
                        buffer.append(Character.toUpperCase(s.charAt(0)));
                        buffer.append(s.substring(1).toLowerCase());
                    }
                    else {
                        buffer.append(Character.toUpperCase(s.charAt(0))).append(s.substring(1));
                    }
                }
            }
        }

        if(name.endsWith("_")) {
            buffer.append("_");
        }
        return buffer.toString();
    }

    /**
     * @param connection
     */
    protected void close(Connection connection) {
        if(connection != null) {
            try {
                connection.close();
            }
            catch(SQLException e) {
            }
        }
    }

    /**
     * @param resultSet
     */
    protected void close(ResultSet resultSet) {
        if(resultSet != null) {
            try {
                resultSet.close();
            }
            catch(SQLException e) {
            }
        }
    }

    /**
     * @param statement
     */
    protected void close(Statement statement) {
        if(statement != null) {
            try {
                statement.close();
            }
            catch(SQLException e) {
            }
        }
    }
}
